package com.dts;

import com.dts.entity.Demo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test {

    public static void main(String[] args) throws SQLException {
        addDemo();
        selectDemo();
//        deleteDemo();
//        updateDemo();
//        stmtTest()
//        prepareStmtTest();
    }

    private static void addDemo() throws SQLException {
        Connection conn = PoolUtils.getConnection();
        Demo demo = new  Demo();
        demo.setId(2);
        demo.setName("张三");
        String sql = "insert into `demo` (id,name) values(?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,demo.getId());
        pstmt.setString(2,demo.getName());
        int i = pstmt.executeUpdate();
        if(i > 0){
            System.out.println("新增成功");
        }
    }

    private static void deleteDemo() throws SQLException {
        Connection conn = PoolUtils.getConnection();
        String sql = "delete from demo where id =?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,1);
        int i = pstmt.executeUpdate();
        if(i > 0){
            System.out.println("删除成功");
        }
    }

    private static void updateDemo() throws SQLException {
        Connection conn = PoolUtils.getConnection();
        String sql = "update demo set name=? where id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "李四");
        pstmt.setInt(2, 90);
        int i = pstmt.executeUpdate();
        if(i > 0){
            System.out.println("修改成功");
        }
    }

    private static void selectDemo() throws SQLException {
        Connection conn = PoolUtils.getConnection();
        String sql = "select id,name from demo ";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        ResultSet resultSet = pstmt.executeQuery();
        List<Demo> list =  new ArrayList<>();
        while (resultSet.next()) {
            Demo demo = new Demo();
            demo.setId(resultSet.getInt("id"));
            demo.setName(resultSet.getString("name"));
            list.add(demo);
        }
        for (Demo demo : list) {
            System.out.println(demo.getId() + ":" + demo.getName());
        }
    }

    public static void stmtTest() throws SQLException {
        Connection conn = PoolUtils.getConnection();
        Statement stmt = conn.createStatement();
        String sql = "INSERT INTO `t_emp`   (`emp_name`, `age`, `sex`, `email`) VALUES    ('张三', 28, '男', 'zhangsan@example.com');";
        int i = stmt.executeUpdate(sql);
        System.out.println(i);

        String querySql = "select * from t_emp";
        ResultSet resultSet = stmt.executeQuery(querySql);
        while (resultSet.next()) {
            System.out.println(resultSet.getString("emp_name"));
        }
        JdbcUtils.close(conn);
    }

    public static void prepareStmtTest() throws SQLException {
        Connection conn = PoolUtils.getConnection();
        String querySql = "select * from t_emp where emp_id =?";
        PreparedStatement ps  = conn.prepareStatement(querySql);
        ps.setInt(1, 1);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            System.out.println(resultSet.getString("emp_name"));
        }
        JdbcUtils.close(conn);
    }
}
